package no.ntnu.fp.storage;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;

import no.ntnu.fp.server.DBConnection;

public class Search {

	/**
	 * Search for in employees.
	 * @param conn Connection to the database
	 * @param searchString Search for this string.
	 * @return Result set that contains matching employees in the database, or null.
	 */
	static ResultSet SearchEmployees(String searchString, Connection conn){
		
		String sql = 
		"SELECT *" +
		"FROM Person P"+
		"WHERE P.Name LIKE '% "+searchString+"%' or P.Email LIKE '% "+searchString+"%' or P.Username LIKE '% "+searchString+"%' ORDER BY P.Name LIKE '% "+searchString+"%';";
		Statement st = null;
		
		try {
			st = conn.createStatement();
		} catch (SQLException e) {

			e.printStackTrace();
			return null;
		}
		
		ResultSet rs = null;
		
		if(st!= null){
			
			try {
				rs = st.executeQuery(sql);
			} catch (SQLException e) {

				e.printStackTrace();
				return null;
			}
			
		}
		return rs;
		
	}
	/**
	 * Input necessary (planned start- and end-; time and date.
	 * @param conn This is an active connection to the database.
	 * @param startDate Start date of the planned appointment.
	 * @param endDate End date of the planned appointment.
	 * @param startTime Start time (hours) of the planned appointment.
	 * @param endTime End time (hours) of the planned appointment.
	 * @return Result set that contains all available rooms for the given planned appointment, or null.
	 */
	static ResultSet FindAvailableRooms(Connection conn, Date startDate, Date endDate, Time startTime, Time endTime, int capacity){
		// TODO legg til et appointmentobjekt som kan overf�re denne informansjonen (om dette lar seg gj�re f�r appointmenten er etablert i DB.
		//DBConnection dbc = new DBConnection();
		
		//ResultSet rs = dbc.selectAvailableRoom(capacity, startDate, endDate, startTime, endTime);
		
		String SQL = "select"+
			"from Rom R, AvtaleTilRom AR"+
			"WHERE R.ID = AR.RomID and ("+startDate+" < AR.StartDato OR "+startDate+">AR.SluttDato or ("+endDate+"=AR.StartDAto AND "+endTime+"<=AR.StartTidspunkt) OR ("+startDate+"=AR.SluttDAto AND "+startTime+">=AR.SluttTidspunkt) );";
		Statement st = null;
		try {
			st = conn.createStatement();
		} catch (SQLException e) {

			e.printStackTrace();
			return null;
		}
		
		ResultSet rs = null;
		
	
		if(st!= null){
			
			try {
				rs = st.executeQuery(SQL);
			} catch (SQLException e) {

				e.printStackTrace();
				return null;
			}
			
		}
		return rs;
	}
	
}
